﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics.Contracts;
using System.Globalization;
using Epi;
using Epi.Data;
using Epi.Fields;

namespace CDC.SampleShell.Infrastructure
{
    public class UnitOfWork<T> : IUnitOfWork<IEntity> 
        where T : Entity
    {
        #region Members
        private readonly IDbDriver _database;
        private readonly Project _project;
        private readonly List<IEntity> _updates = new List<IEntity>();
        private readonly List<IEntity> _inserts = new List<IEntity>();
        private readonly List<IEntity> _deletes = new List<IEntity>();
        #endregion // Members

        #region Constructors
        /// <summary>
        /// Constructor for UnitOfWork
        /// </summary>
        /// <param name="project">The Epi Info project to associate with the unit of work; contains important metadata</param>
        public UnitOfWork(Project project)
        {
            // pre
            Contract.Requires(project != null);

            // post
            Contract.Ensures(_project != null);
            Contract.Ensures(_database != null);

            _project = project;
            _database = _project.CollectedData.GetDatabase();
        }
        #endregion // Constructors

        public void Commit()
        {
            if (_deletes.Count == 0 && _inserts.Count == 0 && _updates.Count == 0) return; // don't run if we have nothing to commit

            using (IDbTransaction transaction = _database.OpenTransaction()) // run all queries on transaction - all or nothing
            {
                string currentUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString();

                try
                {
                    ChangesetSender changesetSender = new ChangesetSender(_database); // create new changeset sender class, used to send update notifications to the server

                    #region Deletion
                    foreach (IEntity entity in _deletes)
                    {
                        // Handles deletion of a record. Notice that there is an iteration over all the tables, including the base table,
                        // to do this. This is a hard-delete; we could soft-delete in Epi Info by setting RecStatus to zero.

                        string globalRecordId = entity.GlobalRecordId.ToString();

                        Query deleteQuery = _database.CreateQuery("DELETE FROM " + entity.Form.TableName + " WHERE GlobalRecordId = @GlobalRecordId");
                        deleteQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, globalRecordId));
                        int rows = _database.ExecuteNonQuery(deleteQuery, transaction);

                        Contract.Assert(rows == 1);

                        foreach (Epi.Page page in entity.Form.Pages)
                        {
                            Query deletePageQuery = _database.CreateQuery("DELETE FROM " + page.TableName + " WHERE GlobalRecordId = @GlobalRecordId");
                            deletePageQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, globalRecordId));
                            rows = _database.ExecuteNonQuery(deletePageQuery, transaction);

                            Contract.Assert(rows == 1);
                        }

                        // log the deletion by the entity's GUID and the base table name it was deleted from
                        DbLogger.Instance.Log(String.Format("Deleted entity {0} from {1}.", globalRecordId, entity.Form.TableName));

                        changesetSender.SendMessage(ChangesetType.DeleteRecord, "Deleted record", globalRecordId, entity.Form.Id, transaction);
                    }
                    #endregion // Deletion

                    #region Updates
                    foreach (IEntity entity in _updates)
                    {
                        string globalRecordId = entity.GlobalRecordId.ToString();
                        View form = entity.Form;

                        if (_database.ToString().Contains("Sql", StringComparison.OrdinalIgnoreCase))
                        {
                            Query updateQuery = _database.CreateQuery("UPDATE " + form.TableName + " SET LastSaveTime = CURRENT_TIMESTAMP, LastSaveLogonName = @LastSaveLogonName WHERE GlobalRecordId = @GlobalRecordId");

                            updateQuery.Parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, currentUser));
                            updateQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, globalRecordId));
                            int rows = _database.ExecuteNonQuery(updateQuery, transaction);
                            Contract.Assert(rows == 1);
                        }
                        else
                        {
                            Query updateQuery = _database.CreateQuery("UPDATE " + form.TableName + " SET LastSaveTime = @LastSaveTime, LastSaveLogonName = @LastSaveLogonName WHERE GlobalRecordId = @GlobalRecordId");

                            updateQuery.Parameters.Add(new QueryParameter("@LastSaveTime", DbType.DateTime, entity.LastSaveTime));
                            updateQuery.Parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, currentUser));
                            updateQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, globalRecordId));
                            int rows = _database.ExecuteNonQuery(updateQuery, transaction);
                            Contract.Assert(rows == 1);
                        }

                        foreach (string fieldName in entity.FieldNames)
                        {
                            Epi.Fields.RenderableField field = form.Fields[fieldName] as Epi.Fields.RenderableField;

                            if (field != null)
                            {
                                string pageTableName = field.Page.TableName;

                                Query updateQuery = _database.CreateQuery("UPDATE " + pageTableName + " SET [" + field.Name + "] = @Parameter WHERE GlobalRecordId = @GlobalRecordId");

                                updateQuery.Parameters.Add(
                                       GetQueryParameterForField(field,
                                            FormatFieldData(form, field.Name, entity.GetData(field.Name)), form, field.Page
                                            ));

                                updateQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, globalRecordId));
                                int rows = _database.ExecuteNonQuery(updateQuery, transaction);
                                Contract.Assert(rows == 1);
                            }
                        }

                        changesetSender.SendMessage(ChangesetType.UpdateRecord, "Updated record", globalRecordId, entity.Form.Id, transaction);
                    }
                    #endregion // Updates

                    #region Inserts
                    foreach (IEntity entity in _inserts)
                    {
                        string globalRecordId = entity.GlobalRecordId.ToString();
                        View form = entity.Form;

                        if (_database.ToString().Contains("Sql", StringComparison.OrdinalIgnoreCase))
                        {
                            Query insertQuery = _database.CreateQuery("INSERT INTO " + form.TableName + " (GlobalRecordId, RecStatus, FKEY, FirstSaveLogonName, FirstSaveTime, LastSaveLogonName, LastSaveTime) " +
                                "VALUES (@GlobalRecordId, @RecStatus, @FKEY, @FirstSaveLogonName, CURRENT_TIMESTAMP, @LastSaveLogonName, CURRENT_TIMESTAMP)");

                            insertQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, globalRecordId));
                            insertQuery.Parameters.Add(new QueryParameter("@RecStatus", DbType.String, 1));
                            insertQuery.Parameters.Add(new QueryParameter("@FKEY", DbType.String, entity.ForeignKey));
                            insertQuery.Parameters.Add(new QueryParameter("@FirstSaveLogonName", DbType.String, currentUser));
                            insertQuery.Parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, currentUser));
                            int rows = _database.ExecuteNonQuery(insertQuery, transaction);
                            Contract.Assert(rows == 1);
                        }
                        else
                        {
                            Query insertQuery = _database.CreateQuery("INSERT INTO " + form.TableName + " (GlobalRecordId, RecStatus, FKEY, FirstSaveLogonName, FirstSaveTime, LastSaveLogonName, LastSaveTime) " +
                                "VALUES (@GlobalRecordId, @RecStatus, @FKEY, @FirstSaveLogonName, @FirstSaveTime, @LastSaveLogonName, @LastSaveTime)");

                            insertQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, globalRecordId));
                            insertQuery.Parameters.Add(new QueryParameter("@RecStatus", DbType.String, 1));
                            insertQuery.Parameters.Add(new QueryParameter("@FKEY", DbType.String, entity.ForeignKey));
                            insertQuery.Parameters.Add(new QueryParameter("@FirstSaveLogonName", DbType.String, currentUser));
                            insertQuery.Parameters.Add(new QueryParameter("@FirstSaveTime", DbType.DateTime, DateTime.Now));
                            insertQuery.Parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, currentUser));
                            insertQuery.Parameters.Add(new QueryParameter("@LastSaveTime", DbType.DateTime, DateTime.Now));
                            int rows = _database.ExecuteNonQuery(insertQuery, transaction);
                            Contract.Assert(rows == 1);
                        }

                        foreach (Epi.Page page in form.Pages)
                        {
                            Query insertQuery = _database.CreateQuery("INSERT INTO " + page.TableName + " (GlobalRecordId) VALUES (@GlobalRecordID)");
                            insertQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, globalRecordId));
                            int rows = _database.ExecuteNonQuery(insertQuery, transaction);
                            Contract.Assert(rows == 1);
                        }

                        foreach (string fieldName in entity.FieldNames)
                        {
                            Epi.Fields.RenderableField field = form.Fields[fieldName] as Epi.Fields.RenderableField;

                            if (field != null)
                            {
                                object fieldData = entity.GetData(fieldName);

                                if (fieldData != null)
                                {
                                    string pageTableName = field.Page.TableName;

                                    Query updateQuery = _database.CreateQuery("UPDATE " + pageTableName + " SET [" + field.Name + "] = @Parameter WHERE GlobalRecordId = @GlobalRecordId");

                                    updateQuery.Parameters.Add(
                                           GetQueryParameterForField(field,
                                                FormatFieldData(form, field.Name, entity.GetData(field.Name)), form, field.Page
                                                ));

                                    updateQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, globalRecordId));
                                    int rows = _database.ExecuteNonQuery(updateQuery, transaction);
                                    Contract.Assert(rows == 1);
                                }
                            }
                        }

                        changesetSender.SendMessage(ChangesetType.InsertRecord, "Inserted record", globalRecordId, entity.Form.Id, transaction);
                    }
                    #endregion // Inserts

                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    // TODO: Do something else here - notify user of problem? Crash?
                }
            }
        }

        public void Discard()
        {
            _updates.Clear();
            _inserts.Clear();
            _deletes.Clear();
        }

        //public void Track(Record entity)
        //{

        //}

        public void Delete(IEntity entity)
        {
            _deletes.Add(entity);
        }

        public void Update(IEntity entity)
        {
            _updates.Add(entity);
        }

        public void Insert(IEntity entity)
        {
            _inserts.Add(entity);
        }

        protected QueryParameter GetQueryParameterForField(Field field, object fieldValue, View destinationForm, Page sourcePage)
        {
            if (!(
                field is GroupField ||
                field is RelatedViewField ||
                field is UniqueKeyField ||
                field is RecStatusField ||
                field is GlobalRecordIdField ||
                fieldValue == null ||
                String.IsNullOrEmpty(fieldValue.ToString()
                )))
            {
                string fieldName = field.Name;
                switch (field.FieldType)
                {
                    case MetaFieldType.Date:
                    case MetaFieldType.DateTime:
                    case MetaFieldType.Time:
                        DateTime dt = DateTime.Parse(fieldValue.ToString(), CultureInfo.InvariantCulture);
                        return new QueryParameter("@" + fieldName, DbType.DateTime, dt);
                    case MetaFieldType.Checkbox:
                        return new QueryParameter("@" + fieldName, DbType.Boolean, Convert.ToBoolean(fieldValue));
                    case MetaFieldType.CommentLegal:
                    case MetaFieldType.LegalValues:
                    case MetaFieldType.Codes:
                    case MetaFieldType.Text:
                    case MetaFieldType.TextUppercase:
                    case MetaFieldType.PhoneNumber:
                    case MetaFieldType.UniqueRowId:
                    case MetaFieldType.ForeignKey:
                    case MetaFieldType.GlobalRecordId:
                    case MetaFieldType.Multiline:
                    case MetaFieldType.GUID:
                        return new QueryParameter("@" + fieldName, DbType.String, fieldValue);
                    case MetaFieldType.Number:
                    case MetaFieldType.YesNo:
                    case MetaFieldType.RecStatus:
                        return new QueryParameter("@" + fieldName, DbType.Single, Convert.ToDouble(fieldValue, System.Globalization.CultureInfo.InvariantCulture));
                    case MetaFieldType.Image:
                        return new QueryParameter("@" + fieldName, DbType.Binary, Convert.FromBase64String(fieldValue.ToString()));
                    case MetaFieldType.Option:
                        return new QueryParameter("@" + fieldName, DbType.Single, fieldValue);
                    default:
                        throw new ApplicationException("Not a supported field type");
                }
            }
            return null;
        }

        protected object FormatFieldData(View destinationForm, string fieldName, object value)
        {
            if (destinationForm.Fields.Contains(fieldName))
            {
                Field field = destinationForm.Fields[fieldName];

                if (field is CheckBoxField)
                {
                    if (value.ToString().Equals("true", StringComparison.OrdinalIgnoreCase))
                    {
                        value = true;
                    }
                    else if (value.ToString().Equals("false", StringComparison.OrdinalIgnoreCase))
                    {
                        value = false;
                    }
                }

                if (field is YesNoField)
                {
                    if (value.ToString().Equals("1", StringComparison.OrdinalIgnoreCase))
                    {
                        value = 1;
                    }
                    else if (value.ToString().Equals("0", StringComparison.OrdinalIgnoreCase))
                    {
                        value = 0;
                    }
                }

                if (field is NumberField && !string.IsNullOrEmpty(value.ToString()))
                {
                    double result = -1;
                    if (double.TryParse(value.ToString(), out result))
                    {
                        value = result;
                    }
                }
            }

            return value;
        }
    }
}
